package oas.db.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import oas.db.LeaveTypeRepository;
import oas.domain.Leave;
import oas.domain.LeaveType;
import oas.web.PaginationSupport;

/**
 * 请假类型DAO
 *
 * @author wjy
 * @version v1.0.0
 */
@Repository
public class JdbcLeaveTypeRepository implements LeaveTypeRepository {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public JdbcLeaveTypeRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private static class LeaveTypesRowMapper implements RowMapper<LeaveType> {

        @Override
        public LeaveType mapRow(ResultSet rs, int rowNum) throws SQLException {

            return new LeaveType(rs.getLong("id"), rs.getString("description"), rs.getBoolean("available"));
        }

    }

    /**
     * 添加一条请假类型并返回为其分配的id
     *
     * @param leaveType 请假类型
     * @return 分配的id
     */
    private long insertLeaveTypeAndReturnId(LeaveType leaveType) {
        SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("leavetype");
        jdbcInsert.setGeneratedKeyName("id");
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("description", leaveType.getDescription());
        args.put("available", 1);
        long leaveTypeId = jdbcInsert.executeAndReturnKey(args).longValue();
        return leaveTypeId;
    }

    /**
     * 查找指定页面大小下某一页的数据
     *
     * @param pageNo
     * @param pageSize
     * @return 一页请假类新
     */
    @Override
    public PaginationSupport<LeaveType> findPage(int pageNo, int pageSize) {

        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<LeaveType>(new ArrayList<LeaveType>(), 0);
        }

        List<LeaveType> items = jdbcTemplate.query(SELECT_LEAVETYPES_PAGE, new LeaveTypesRowMapper(), pageSize, startIndex);
        PaginationSupport<LeaveType> ps = new PaginationSupport<LeaveType>(items, totalCount, pageSize, startIndex);
        return ps;
    }

    /**
     * 请假类型总数。被删除的请假类型不会计入其中
     *
     * @return 请假类型总数
     */
    @Override
    public long count() {

        return jdbcTemplate.queryForLong("select count(id) from leavetype where available = 1");
    }

    /**
     * 删除一个请假类型
     *
     * @param id 要删除请假类型的id
     */
    @Override
    public void deleteOne(long id) {

        jdbcTemplate.update(DELETE_BY_ID, id);

    }

    /**
     * 更新一个请假类型
     *
     * @param leaveType 新的请假类型
     */
    @Override
    public void update(LeaveType leaveType) {

        jdbcTemplate.update(UPDATE_BY_ID, leaveType.getDescription(), leaveType.getId());
    }

    /**
     * 添加一个新的请假类型
     *
     * @param leaveType 新的请假类型
     * @return 添加进数据库的请假类型
     */
    @Override
    public LeaveType save(LeaveType leaveType) {
        Long id = insertLeaveTypeAndReturnId(leaveType);
        leaveType.setId(id);
        return leaveType;
    }

    /**
     * 通过id查询请假类型
     *
     * @param id
     * @return 请假类型
     * @author Dzq
     */
    @Override
    public LeaveType findById(long id) {
        LeaveType leaveType = null;
        try {
            leaveType = jdbcTemplate.queryForObject("select id, description, available from leavetype"
                    + " where id=?", new LeaveTypesRowMapper(), id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return leaveType;
    }

    /**
     * 查询所有请假类型，用于请假时的下拉框显示
     *
     * @return 请假类型列表
     * @author Dzq
     */
    @Override
    public List<LeaveType> findAll() {
        List<LeaveType> items = jdbcTemplate.query(SELECT_LEAVETYPES, new LeaveTypesRowMapper());
        return items;
    }


    private static final String SELECT_LEAVETYPES = "select id, description, available "
            + "from leavetype "
            + "where available = 1 ";

    private static final String SELECT_LEAVETYPES_PAGE = SELECT_LEAVETYPES
            + " order by id limit ? offset  ?";

    private static final String DELETE_BY_ID = "update leavetype set available = 0 where id = ?;";

    private static final String UPDATE_BY_ID = "update leavetype set description = ? where id = ?;";


}
